EXISTS Operator
This lesson discusses the EXISTS operator.
In this lesson we’ll learn about the boolean operator EXISTS and its complement, NOT EXISTS. The EXISTS operator is usually used to test if a subquery returns any rows or none at all.
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/33lesson.sh and wait for the MySQL prompt to start-up.
-
Let’s start with a simple example. We’ll check if our table DigitalAssets has any account owned by the actor George Clooney. If yes, we print the list of all the actors from our Actors table. Granted, the query doesn’t make much sense but bear with me as we’ll see more useful applications of the EXISTS operator in later lessons. The query is shown below:
SELECT *
FROM Actors
WHERE EXISTS ( SELECT *
FROM DigitalAssets
WHERE BINARY URL LIKE "%clooney%");
The outcome is an empty set because we don’t have any entry for George Clooney in our DigitalAssets table. The subquery selects all the columns, but what the subquery selects is irrelevant because the EXISTS operator only checks for the existence of any rows from the result returned by the subquery. The WHERE condition of the outer query becomes false and the overall query returns an empty result set too.
Note we have used the BINARY operator to make the comparison case-insensitive which is not required if the collation is already set to be case-insensitive for the database.
-
We add the NOT operator to the EXISTS clause and see the entire Actors table print out.
SELECT *
FROM Actors
WHERE NOT EXISTS ( SELECT *
FROM DigitalAssets
WHERE BINARY URL LIKE "%clooney%");
